#!/bin/sh
#set -x
###########################################################################################
#author  :      dengxh
#date:      2009/03/17
###########################################################################################

VERSION="V1.0"
usage()
{
	echo ""
	echo " fix ODS_SRC_DT issue about BF_AGT_DEP_ACCT_SAP,BF_EVT_DEP_TDP_TDACNADV"
    echo "BF_EVT_DEP_TDE_TDACNADV"
    echo "    sh $0 -old oldmodel -cnew newmodel"
    echo "    sh $0 -old odsdata/odsdata@odsbptdb -new odsbdata/odsbdata@odsbptdb"
    echo " "
}
show_version()
{

    echo "H010_Truncate_table 1.0.1 final . 2009-03-20"
    echo "Written by Deng XiaoHui."

    echo " "
    echo "description:Truncate ORACLE table ,with reuse storge option "
    echo "       reuse storge is usefull when dealing with large table"
    echo " "
}

param_num=$#

       if [ ${param_num} != "4" ] 
      then
               usage
               exit 90
       fi

while true
do 

        if [ "$1"x = "-old"x ]||[ "$1"x = "-o"x ]
    then
        shift 1
        OLD_ORACLE_USER=`echo $1 | awk -F '[@|/]' '{print $1}'`
        OLD_ORACLE_PASSWD=`echo $1 | awk -F '[@|/]' '{print $2}'`
        OLD_ORACLE_DBSID=`echo $1 | awk -F '[@|/]' '{print $3}'`
    fi
		
		  if [ "$1"x = "-new"x ]||[ "$1"x = "-n"x ]
    then
        shift 1
        NEW_ORACLE_USER=`echo $1 | awk -F '[@|/]' '{print $1}'`
        NEW_ORACLE_PASSWD=`echo $1 | awk -F '[@|/]' '{print $2}'`
        NEW_ORACLE_DBSID=`echo $1 | awk -F '[@|/]' '{print $3}'`
    fi

	shift 1
	if [ $# -eq 0 ]
	then
		break
	fi
done


#	if [ ${param_num} != "8" ] && [ ${param_num} != "10" ]
#	then
#		usage
#		exit 90
#	fi

Date=`date +%Y%m%d%T`
	echo "Starting grant select to new model user"
sqlplus -s $OLD_ORACLE_USER/$OLD_ORACLE_PASSWD@$OLD_ORACLE_DBSID<< TMP 
SET TIMING ON
SET HEAD OFF
set echo ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
GRANT select ON F_DEP_SAP_ACCT TO ${NEW_ORACLE_USER};
GRANT select ON F_DEP_TDE_ACNADV TO ${NEW_ORACLE_USER};
GRANT select ON F_DEP_TDP_ACNADV TO ${NEW_ORACLE_USER};
exit;
TMP

ret=$?


if [ $ret != 0 ]
then
	echo "GRANT table error,return error code 92,"
	exit 92
else
	echo " "  #done .truncate table successfully
fi


	echo "Starting truncate table  $ORACLE_TABLE_NAME"
sqlplus -s $NEW_ORACLE_USER/$NEW_ORACLE_PASSWD@$NEW_ORACLE_DBSID<< TMP 
SET TIMING OFF
SET HEAD OFF
set echo ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
select 'UPDATING BF_AGT_DEP_ACCT_SAP' FROM DUAL;
UPDATE BF_AGT_DEP_ACCT_SAP A
   SET ODS_SRC_DT = ( CASE WHEN LENGTH(ODS_SRC_DT)=8 THEN TO_CHAR(TO_DATE(ODS_SRC_DT,'YYYYMMDD'),'YYYY-MM-DD') ELSE 
           (SELECT SUBSTR(ODS_SRC_DT, 1, 10)
                       FROM ${OLD_ORACLE_USER}.F_DEP_SAP_ACCT B
                      WHERE A.ACCT_NO = B.CCBS_ACCT_NO
                        AND A.CURR_IDEN = B.SA_CURR_IDEN
                        AND A.CURR_CD = B.SA_CURR_COD) END)
 WHERE (ODS_SRC_DT LIKE '%--%' OR LENGTH(ODS_SRC_DT)=8); 
 commit;
 select 'UPDATING BF_EVT_DEP_TDP_TDACNADV' FROM DUAL;
UPDATE BF_EVT_DEP_TDP_TDACNADV A
   SET ODS_SRC_DT = ( CASE WHEN LENGTH(ODS_SRC_DT)=8 THEN TO_CHAR(TO_DATE(ODS_SRC_DT,'YYYYMMDD'),'YYYY-MM-DD') ELSE  
		(SELECT SUBSTR(ODS_SRC_DT, 1, 10)
                       FROM ${OLD_ORACLE_USER}.F_DEP_TDE_ACNADV B
                      WHERE A.ACCT_NO = B.TD_TD_ACCT_NO
                        AND A.ADV_DEP_NO = B.TD_ADV_NO) END )
 WHERE (ODS_SRC_DT LIKE '%--%' OR LENGTH(ODS_SRC_DT)=8);  
commit;
 select 'UPDATING BF_EVT_DEP_TDP_TDACNADV' FROM DUAL;
UPDATE BF_EVT_DEP_TDP_TDACNADV A
   SET ODS_SRC_DT =( CASE WHEN LENGTH(ODS_SRC_DT)=8 THEN TO_CHAR(TO_DATE(ODS_SRC_DT,'YYYYMMDD'),'YYYY-MM-DD') ELSE 
              (SELECT SUBSTR(ODS_SRC_DT, 1, 10)
                       FROM ${OLD_ORACLE_USER}.F_DEP_TDP_ACNADV B
                      WHERE A.ACCT_NO = B.TD_TD_ACCT_NO
                        AND A.ADV_DEP_NO = B.TD_ADV_NO) END )
 WHERE (ODS_SRC_DT LIKE '%--%' OR LENGTH(ODS_SRC_DT)=8); 
commit;
exit;
TMP

ret=$?


if [ $ret != 0 ]
then
	echo "truncate table error,return error code 92,"
	exit 92
else
	echo " "  #done .truncate table successfully
fi

exit 0
